using Microsoft.EntityFrameworkCore;
using UwinEducation.Domain.Entities;
using UwinEducation.Domain.IRepositories;
using UwinEducation.Infrastructure.Persistence;
using UwinEducation.Shared.Enums;
using UwinEducation.Shared.Models;

namespace UwinEducation.Infrastructure.Repositories
{
    /// <summary>
    /// 学生课表仓储实现
    /// </summary>
    public class StudentScheduleRepository
        : Repository<StudentSchedule, Guid>,
            IStudentScheduleRepository
    {
        public StudentScheduleRepository(ApplicationDbContext context)
            : base(context) { }

        /// <summary>
        /// 获取学生课表分页列表
        /// </summary>
        public async Task<PagedResult<StudentSchedule>> GetPagedListAsync(
            StudentSchedulePageRequest pageRequest
        )
        {
            var query = BaseQuery().AsNoTracking()
                .Include(ss => ss.Student)
                .Include(ss => ss.TeacherSchedule)
                .Include(ss => ss.TeacherScheduleDetails)
                .AsQueryable();

            if (pageRequest.StudentId.HasValue)
            {
                query = query.Where(ss => ss.StudentId == pageRequest.StudentId);
            }

            if (pageRequest.TeacherScheduleId.HasValue)
            {
                query = query.Where(ss =>
                    ss.TeacherScheduleDetailsId == pageRequest.TeacherScheduleId
                );
            }

            if (pageRequest.TeacherScheduleGroupId.HasValue)
            {
                query = query.Where(ss =>
                    ss.TeacherScheduleId == pageRequest.TeacherScheduleGroupId
                );
            }
            if (pageRequest.Status.HasValue)
            {
                query = query.Where(ss => ss.Status == pageRequest.Status);
            }

            //if (pageRequest.StartDate.HasValue)
            //{
            //    query = query.Where(ss =>
            //        ss.TeacherSchedule.StartTime.Date >= pageRequest.StartDate.Value.Date
            //    );
            //}

            //if (pageRequest.EndDate.HasValue)
            //{
            //    query = query.Where(ss =>
            //        ss.TeacherSchedule.EndTime.Date <= pageRequest.EndDate.Value.Date
            //    );
            //}

            var total = await query.CountAsync();
            var items = await query
                .OrderBy(ss => ss.TeacherSchedule.StartTime)
                .Skip((pageRequest.PageIndex - 1) * pageRequest.PageSize)
                .Take(pageRequest.PageSize)
                .ToListAsync();

            return new PagedResult<StudentSchedule> { Total = total, Items = items };
        }

        /// <summary>
        /// 获取学生课表详情
        /// </summary>
        public async Task<StudentSchedule> GetDetailsByIdAsync(Guid id)
        {
            return await _context
                .StudentSchedules.AsNoTracking()
                .Include(ss => ss.Student)
                .Include(ss => ss.TeacherSchedule)
                .FirstOrDefaultAsync(ss => ss.Id == id);
        }

        /// <summary>
        /// 获取学生的所有课表
        /// </summary>
        public async Task<List<StudentSchedule>> GetByStudentIdAsync(Guid studentId)
        {
            return await _context
                .StudentSchedules.AsNoTracking()
                .Include(ss => ss.TeacherSchedule)
                .Include(ss => ss.TeacherScheduleDetails)
                .Where(ss => ss.StudentId == studentId)
                .Where(ss => ss.TeacherSchedule != null && !ss.TeacherSchedule.IsDeleted)
                .OrderBy(ss => ss.TeacherSchedule.StartTime)
                .ToListAsync();
        }

        /// <summary>
        /// 获取指定日期范围内的学生课表
        /// </summary>
        public async Task<List<StudentSchedule>> GetByDateRangeAsync(
            Guid studentId,
            DateTime? startDate,
            DateTime? endDate
        )
        {
            var query = _context
                .StudentSchedules.AsNoTracking()
                .Include(ss => ss.TeacherSchedule)
                .Include(ss => ss.TeacherScheduleDetails)
                .Include(ss => ss.Student)
                .Where(ss => ss.StudentId == studentId)
                .Where(ss => ss.TeacherSchedule != null && !ss.TeacherSchedule.IsDeleted);

            if (startDate.HasValue)
            {
                query = query.Where(ss => 
                    ss.TeacherScheduleDetails.StartTime.Date >= startDate.Value.Date);
            }

            if (endDate.HasValue)
            {
                query = query.Where(ss => 
                    ss.TeacherScheduleDetails.EndTime.Date <= endDate.Value.Date);
            }

            return await query
                .OrderBy(ss => ss.TeacherScheduleDetails.StartTime)
                .ToListAsync();
        }

        public async Task<List<StudentSchedule>> GetByDateRangeAsync(
            Guid studentId,
            Guid? teacherScheduleId,
            DateTime? startDate,
            DateTime? endDate
        )
        {
            var query = this.BaseQuery().AsNoTracking()
                .Include(ss => ss.TeacherSchedule)
                .Include(ss => ss.TeacherSchedule.Classroom)
                .Include(ss => ss.TeacherSchedule.Course)
                .Include(ss => ss.TeacherSchedule.Teacher)
                .Include(ss => ss.TeacherScheduleDetails)
                .Include(ss => ss.Student)
                .Where(ss => ss.StudentId == studentId)
                .Where(ss => ss.TeacherSchedule != null && !ss.TeacherSchedule.IsDeleted);

            if (teacherScheduleId.HasValue)
            {
                query = query.Where(ss => ss.TeacherScheduleId == teacherScheduleId);
            }

            if (startDate.HasValue)
            {
                query = query.Where(ss => 
                    ss.TeacherScheduleDetails.StartTime.Date >= startDate.Value.Date);
            }

            if (endDate.HasValue)
            {
                query = query.Where(ss => 
                    ss.TeacherScheduleDetails.EndTime.Date <= endDate.Value.Date);
            }

            return await query
                .OrderBy(ss => ss.TeacherScheduleDetails.StartTime)
                .ToListAsync();
        }

        /// <summary>
        /// 根据教师课表ID获取学生课表
        /// </summary>
        public async Task<StudentSchedule> GetByTeacherScheduleIdAsync(
            Guid teacherScheduleId,
            Guid studentId
        )
        {
            return await _context
                .StudentSchedules.AsNoTracking()
                .Include(ss => ss.Student)
                .Include(ss => ss.TeacherSchedule)
                .FirstOrDefaultAsync(ss =>
                    ss.TeacherScheduleDetailsId == teacherScheduleId && ss.StudentId == studentId
                );
        }

        /// <summary>
        /// 批量获取学生课表
        /// </summary>
        public async Task<List<StudentSchedule>> GetByTeacherScheduleIdsAsync(
            List<Guid> teacherScheduleIds,
            Guid studentId
        )
        {
            return await _context
                .StudentSchedules.AsNoTracking()
                .Include(ss => ss.Student)
                .Include(ss => ss.TeacherSchedule)
                .Where(ss =>
                    teacherScheduleIds.Contains(ss.TeacherScheduleDetailsId)
                    && ss.StudentId == studentId
                )
                .ToListAsync();
        }

        /// <summary>
        /// 批量获取学生课表
        /// </summary>
        public async Task<List<StudentSchedule>> GetByTeacherScheduleIdAsync(Guid teacherScheduleId)
        {
            return await _context
                .StudentSchedules.AsNoTracking()
                .Include(ss => ss.Student)
                .Include(ss => ss.TeacherSchedule)
                .Where(ss => ss.TeacherScheduleDetailsId == teacherScheduleId)
                .ToListAsync();
        }

        /// <summary>
        /// 获取教师课表组已使用的容量
        /// </summary>
        public async Task<int> GetUsedCapacityByTeacherScheduleGroupIdAsync(
            Guid teacherScheduleGroupId
        )
        {
            return await _context
                .StudentSchedules.AsNoTracking()
                .Where(ss => ss.TeacherScheduleId == teacherScheduleGroupId)
                .GroupBy(ss => new { ss.TeacherScheduleId, ss.StudentId }) // 按两个字段分组
                .Select(g => g.Key.StudentId) // 选择每组的StudentId
                .Distinct() // 去重
                .CountAsync(); // 计数不同学生的数量
        }

        /// <summary>
        /// 获取每个课表的报名人数
        /// </summary>
        public async Task<
            List<(Guid TeacherScheduleId, int EnrolledCount)>
        > GetEnrolledCountByTeacherScheduleIdsAsync(List<Guid> teacherScheduleIds)
        {
            var filteredSchedules = _context
                .StudentSchedules.AsNoTracking()
                .Include(ss => ss.TeacherScheduleDetails).Include(ss => ss.Student)
                .Where(ss =>
                    teacherScheduleIds.Contains(ss.TeacherScheduleId)
                    && ss.TeacherScheduleDetails.Status == ScheduleStatus.Normal&&!ss.Student.IsDeleted
                )
                .Select(ss => new { ss.TeacherScheduleId, ss.StudentId })
                .Distinct()
                .ToList(); // 执行查询，拉取数据到内存

            var result = filteredSchedules
                .GroupBy(ss => ss.TeacherScheduleId)
                .Select(g => new
                {
                    TeacherScheduleGroupId = g.Key,
                    EnrolledCount = g.Select(x => x.StudentId).ToList().Count,
                });

            return result.Select(x => (x.TeacherScheduleGroupId, x.EnrolledCount)).ToList();
        }

        public async Task<
            List<(Guid TeacherScheduleDetailsId, int EnrolledCount)>
        > GetEnrolledCountByTeacherScheduleDetailsIdsAsync(List<Guid> teacherScheduleDetailsIds)
        {
            var filteredSchedules = _context
                .StudentSchedules.AsNoTracking()
                .Include(ss => ss.TeacherScheduleDetails).Include(ss => ss.Student)
                .Where(ss =>
                    teacherScheduleDetailsIds.Contains(ss.TeacherScheduleDetailsId)
                    && ss.TeacherScheduleDetails.Status == ScheduleStatus.Normal && !ss.Student.IsDeleted
                )
                .Select(ss => new { ss.TeacherScheduleDetailsId, ss.StudentId })
                .Distinct()
                .ToList(); // 执行查询，拉取数据到内存

            var result = filteredSchedules
                .GroupBy(ss => ss.TeacherScheduleDetailsId)
                .Select(g => new
                {
                    TeacherScheduleGroupId = g.Key,
                    EnrolledCount = g.Select(x => x.StudentId).ToList().Count,
                });

            return result.Select(x => (x.TeacherScheduleGroupId, x.EnrolledCount)).ToList();
        }

        
        /// <summary>
        /// 获取学生所有现有课表
        /// </summary>
        public async Task<List<StudentSchedule>> GetByStudentIdAsync(
            Guid studentId,
            DateTime startTime,
            DateTime endTime
        )
        {
            return await _context
                .StudentSchedules.AsNoTracking()
                .Include(ss => ss.TeacherScheduleDetails)
                .Include(ss => ss.TeacherSchedule.Course)
                .Include(ss => ss.TeacherSchedule.Classroom)
                .Include(ss => ss.TeacherSchedule.Teacher)
                .Where(ss =>
                    ss.StudentId == studentId
                    && ss.TeacherScheduleDetails.StartTime >= startTime
                    && ss.TeacherScheduleDetails.EndTime <= endTime
                )
                .ToListAsync();
        }

        /// <summary>
        /// 获取学生课表统计分页列表
        /// </summary>
        public async Task<
            PagedResult<(StudentSchedule studentSchedule, int courseCount)>
        > GetStatisticsPagedListAsync(StudentScheduleStatisticsPageRequest pageRequest)
        {
            //先计算CourseCount
            var courseCount = await _context
                .StudentSchedules.AsNoTracking()
                .Include(ss => ss.TeacherSchedule)
                .Include(ss => ss.TeacherSchedule.Teacher)
                .Where(ss => ss.StudentId == pageRequest.StudentId)
                .GroupBy(ss => ss.TeacherScheduleId)
                .Select(g => new
                {
                    TeacherScheduleId = g.Key,
                    StudentId = g.First().StudentId,
                    CourseCount = g.Count(),
                })
                .ToListAsync();

            //获取StudentSchedule集合，根据StudentId和TeacherScheduleId分组,并计算CourseCount=统计teacherScheduleDetails的count
            var query = _context
                .StudentSchedules.AsNoTracking()
                .Include(ss => ss.Student)
                .Include(ss => ss.TeacherSchedule)
                .Include(ss => ss.TeacherSchedule.Course)
                .Include(ss => ss.TeacherSchedule.Teacher)
                .Include(ss => ss.TeacherScheduleDetails)
                .Where(ss => ss.StudentId == pageRequest.StudentId)
                .Where(ss =>
                    !pageRequest.StartTime.HasValue
                    || ss.TeacherScheduleDetails.StartTime >= pageRequest.StartTime.Value
                )
                .Where(ss =>
                    !pageRequest.EndTime.HasValue
                    || ss.TeacherScheduleDetails.EndTime <= pageRequest.EndTime.Value
                )
                .GroupBy(ss => new { ss.TeacherScheduleId, ss.StudentId })
                .Select(g => g.First()); // 从每个分组中选择第一条记录

            var total = await query.CountAsync();
            var items = await query
                .Skip((pageRequest.PageIndex - 1) * pageRequest.PageSize)
                .Take(pageRequest.PageSize)
                .ToListAsync();

            var result = items
                .Select(x =>
                    (
                        x,
                        courseCount
                            .FirstOrDefault(c =>
                                c.TeacherScheduleId == x.TeacherScheduleId
                                && c.StudentId == x.StudentId
                            )
                            .CourseCount
                    )
                )
                .ToList();

            return new PagedResult<(StudentSchedule studentSchedule, int courseCount)>
            {
                Total = total,
                Items = result,
            };
        }

        /// <summary>
        /// 获取学生课表统计列表
        /// </summary>

        /// <summary>
        /// 获取指定时间范围内有冲突的课表
        /// </summary>
        /// <param name="studentId">学生ID</param>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <param name="excludeScheduleId">需要排除的课表ID（可选）</param>
        /// <returns>冲突的课表列表</returns>
        public async Task<List<StudentSchedule>> GetConflictSchedulesAsync(
            Guid studentId,
            DateTime startTime,
            DateTime endTime,
            Guid? excludeScheduleId = null
        )
        {
            var query = _context
                .StudentSchedules.AsNoTracking()
                .Include(ss => ss.TeacherScheduleDetails)
                .Include(ss => ss.TeacherSchedule)
                .Include(ss => ss.Student)
                .Where(ss =>
                    ss.StudentId == studentId
                    && (
                        // 新开始时间在现有时间段内
                        (
                            startTime >= ss.TeacherScheduleDetails.StartTime
                            && startTime <= ss.TeacherScheduleDetails.EndTime
                        )
                        ||
                        // 新结束时间在现有时间段内
                        (
                            endTime >= ss.TeacherScheduleDetails.StartTime
                            && endTime <= ss.TeacherScheduleDetails.EndTime
                        )
                        ||
                        // 新时间段完全包含现有时间段
                        (
                            startTime <= ss.TeacherScheduleDetails.StartTime
                            && endTime >= ss.TeacherScheduleDetails.EndTime
                        )
                    )
                );

            // 如果需要排除某个课表
            if (excludeScheduleId.HasValue)
            {
                query = query.Where(ss => ss.Id != excludeScheduleId.Value);
            }

            return await query.ToListAsync();
        }

        /// <summary>
        /// 获取教师课表集合
        /// </summary>
        public async Task<List<StudentSchedule>> GetByTeacherIdAndDateRangeAsync(
            Guid teacherId,
            DateTime startDate,
            DateTime endDate
        )
        {
            return await _context
                .StudentSchedules
                .AsNoTracking()
                .Include(ss => ss.TeacherSchedule)
                .Include(ss => ss.TeacherSchedule.TeacherScheduleDetails)
                .Include(ss => ss.TeacherSchedule.Course)
                .Include(ss => ss.TeacherSchedule.Classroom)
                .Include(ss => ss.Student)
                .Where(ss =>
                    ss.TeacherSchedule.TeacherId == teacherId
                    && ss.TeacherScheduleDetails.StartTime >= startDate
                    && ss.TeacherScheduleDetails.EndTime <= endDate
                    && ss.TeacherSchedule != null 
                    && !ss.TeacherSchedule.IsDeleted
                )
                .ToListAsync();
        }
        /// <summary>   
        /// 根据学生ID和教师课表ID获取学生课表
        /// </summary>
        /// <param name="studentId">学生ID</param>
        /// <param name="teacherScheduleId">教师课表ID</param>
        /// <returns>学生课表列表</returns>
        public async Task<List<StudentSchedule>> GetByStudentIdAndTeacherScheduleIdAsync(Guid studentId, Guid teacherScheduleId)
        {
            return await _context
                .StudentSchedules
                .AsNoTracking()
                .Where(ss => 
                    ss.StudentId == studentId && 
                    ss.TeacherScheduleId == teacherScheduleId)
                .ToListAsync();
        }

        /// <summary>
        /// 根据学生ID和教师课表ID删除学生课表
        /// </summary>
        /// <param name="studentId"></param>
        /// <param name="teacherScheduleId"></param>
        /// <returns></returns>
        public async Task DeleteByStudentIdAndTeacherScheduleIdAsync(
            Guid studentId,
            Guid teacherScheduleId
        )
        {
            await _context
                .StudentSchedules.AsNoTracking()
                .Where(ss =>
                    ss.StudentId == studentId && ss.TeacherScheduleId == teacherScheduleId
                )
                .ExecuteDeleteAsync();
        }

        /// <summary>
        /// 根据教师课表ID获取所有学生（去重）
        /// </summary>
        /// <param name="teacherScheduleId">教师课表ID</param>
        /// <returns>学生列表</returns>
        public async Task<List<Student>> GetDistinctStudentsByTeacherScheduleIdAsync(Guid teacherScheduleId)
        {
            return await _context
                .StudentSchedules
                .AsNoTracking()
                .Include(ss => ss.Student)
                .Where(ss => ss.TeacherScheduleId == teacherScheduleId && !ss.Student.IsDeleted)
                .Select(ss => ss.Student)
                .Distinct()
                .ToListAsync();
        }
    }
}
